<?php

namespace App\Jobs;

use Illuminate\Bus\Queueable;
use Illuminate\Queue\SerializesModels;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use App\Models\AmazonPlanStatus;
use App\Models\AmazonSkulist;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Redis;
use PhpParser\Node\Stmt\TryCatch;

class ExcelTaskJob implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    public $tries = 2;

    protected $params;
    /**
     * Create a new job instance.
     *
     * @return void
     */
    public function __construct($params)
    {
        //
        $this->params = $params;
    }

    /**
     * Execute the job.
     *
     * @return void
     */
    public function handle()
    {
        //
        // echo '队列ExcelTaskJob';
        // var_dump($this->params);
        $params = $this->params;
        if(empty($params)){
            echo  '无数据队列';
        }else{
            $insert['name'] = $params['title'];
            $insert['type'] = $params['type'];
            $insert['user_id'] = $params['user_id'];
            $insert['create_time'] = date('Y-m-d H:i:s',time());
            $insert['status'] = 0;
            $insert['msg'] = '生成中';
            $insert['path'] = '';
    
            $task_id = Db::table('excel_task')->insertGetId($insert);
    
            $params['task_id'] = $task_id;
    
            try {
                //消化队列
                // var_dump($params);
                // exit;
                // $dts =  json_decode(json_encode($params['data']), true);
                // foreach ($dts as $v) {
                //     # code...
                //     if(isset($v['custom_sku'])){
                //         if($v['custom_sku']=='AHDP100059-RBL-2XL'){
                //             $data[0] = $v;
                //         }
                //     }else{
                //         if($v['spu']=='合计'){
                //             $data[1] = $v;
                //         }
                //     }
    
    
    
     
                   
                // }
    
                // $params['data'] = $data;
    
                $this->place_excel_expord($params);
            } catch(\Exception $e){
    
                //消化失败，记录错误日志
                $errmsg = $e->getMessage().'；错误行：'.$e->getLine();
                $add['job'] = 'excel_task';
                $add['time'] = time();
                $add['msg'] =  $errmsg;
                $add['data'] = json_encode($this->params);
                Db::table('job_err_log')->insert($add);
    
                $update['status'] = 2;
                $update['path'] = '';
                $update['msg'] = $errmsg;
                Db::table('excel_task')->where('id',$task_id)->update($update);
            }
        }
     
    }


    //excel导出 
    public function place_excel_expord($params){

        
        $tltle = $params['title'];
        // var_dump(  $tltle);
        $path = '/var/www/test/xin/public/admin/export/'.$tltle.'.xlsx';
        $path_b = 'admin/export/'.$tltle.'.xlsx';

        $task_id = $params['task_id'];
        // $insert['name'] = $params['title'];
        // $insert['type'] = $params['type'];
        // $insert['user_id'] = $params['user_id'];
        // $insert['create_time'] = date('Y-m-d H:i:s',time());
        // $insert['status'] = 0;
        // $insert['msg'] = '生成中';
        // $insert['path'] = '';
        // $task_id = Db::table('excel_task')->insertGetId($insert);



        $title_list = $params['title_list'];
        $protdata = json_decode(json_encode($params['data']), true);

        $obj = new \PHPExcel();

        $fileName = $tltle;
        $fileType = 'xlsx';

        // 以下内容是excel文件的信息描述信息
        $obj->getProperties()->setTitle($tltle); //设置标题

        // 设置当前sheet
        $obj->setActiveSheetIndex(0);

        // 设置当前sheet的名称
        $obj->getActiveSheet()->setTitle($tltle);

        /*以下就是对处理Excel里的数据， 横着取数据，主要是这一步，其他基本都不要改*/
        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ", "CA", "CB", "CC", "CD", "CE", "CF", "CG", "CH", "CI", "CJ", "CK", "CL", "CM", "CN", "CO", "CP", "CQ", "CR", "CS", "CT", "CU", "CV", "CW", "CX", "CY", "CZ", "DA", "DB", "DC", "DD", "DE", "DF", "DG", "DH", "DI", "DJ");
        // 设置excel标题


        $index = 0;
        $data  = [];

        //过滤数据
        foreach ($protdata  as $pk => $onedata) {
            //过滤数据
            $one_new_data = [];
            foreach ($title_list as $k =>$v) {
                foreach ($onedata as $key => $value) {
                    # code...
                    
                    if($k!=''&&$k==$key){
                        // if(isset($one_new_data[$k])){
                        //     $update['status'] = 2;
                        //     $update['path'] = '';
                        //     $update['msg'] =  '该数据有误'.$k;
                        //     Db::table('excel_task')->where('id',$task_id)->update($update);
                        //     exit;
                        // }
                        $one_new_data[$k] = $value;
                    }
                }
            }
            $data[$pk]= $one_new_data;
                # code...
        }

//         echo '第一';


        // 填充第一行数据
        foreach ($title_list as $k =>$v) {
        $obj->getActiveSheet()->setCellValue($cellName[$index] . '1', $v);
        $index++;
        }



//         echo '第二';

        $i=0;

        // 填充第n(n>=2, n∈N*)行数据
        // var_dump($data);
        foreach ($data as $v) {
            # code...
            // echo $v[$key[$i]];
            // var_dump($v);
            $si = 0;
            foreach ($v as $sk => $sv) {
                # code...
                $obj->getActiveSheet()->setCellValue($cellName[$si] . ($i + 2), $sv, \PHPExcel_Cell_DataType::TYPE_STRING);
                if($sk=='img'){
                    if(!empty($sv)){
                        // echo '图片';
                        $name = str_replace('http://116.62.169.253/','/var/www/online/xin/public/admin/excel_img/',$sv);
                        // echo $name;
                        if($name){
                            // try {
                            //     //code...
                            //     echo '图片1';
                            //  try {
                                $a = $this->httpcopy($sv,$name);
                                if($a['err']){
                                    // $update['status'] = 2;
                                    // $update['path'] = '';
                                    // $update['msg'] =  $a['data'];
                                    // Db::table('excel_task')->where('id',$task_id)->update($update);
                                    // exit;
                                    $imgtype = 'jpg';
                                    if(strpos($sv, 'png')){
                                        $imgtype = 'png';
                                    }
                                    $name = '/var/www/online/xin/public/admin/excel_img/'.rand(1,100).time().'.'.$imgtype;
                                    $ab = $this->httpcopy($sv,$name);
                                    if($ab['err']){
//                                        $update['status'] = 2;
//                                        $update['path'] = '';
//                                        $update['msg'] =  $ab['data'];
//                                        Db::table('excel_task')->where('id',$task_id)->update($update);
//                                        exit;
                                        $name = '';
                                    }
                                }
                            // } catch (\Throwable $th) {
                            //     //throw $th;
                            //     // var_dump($th);
                            //     // $update['status'] = 2;
                            //     // $update['path'] = '';
                            //     // $update['msg'] =  $th->getMessage();
                            //     // Db::table('excel_task')->where('id',$task_id)->update($update);
                            //     // exit;
                            //     // $a = $name;
                            // }
                            
                            // } catch (\Throwable $th) {
                            //     //throw $th;
                            //     var_dump($th);
                            // }
                          
                            // echo '图片2';
                            // $a = '';
                            if(is_file($name)){
                                // $sv = './admin/img/d1662363206_cangsou.jpg';
                                try {
                                    //code...
                                    $sv = $name;
                                    $objDrawing  = new \PHPExcel_Worksheet_Drawing();
                                    // 获取图片地址
                                    $objDrawing->setPath($sv);
                                    // 设置图片存放在表格的位置
                                    $objDrawing->setCoordinates($cellName[$si] .($i + 2));
                                    // 设置表格宽度
                                    $obj->getActiveSheet()->getColumnDimension($cellName[$si])->setWidth(20);
                                    // 设置表格高度
                                    $obj->getActiveSheet()->getRowDimension(($i + 2))->setRowHeight(60);
                                    // 设置图片宽
                                    //$objDrawing->setWidth(80);
                                    // 设置图片高
                                    $objDrawing->setHeight(60);
                                    // 设置X方向偏移量
                                    $objDrawing->setOffsetX(10);
                                    // 设置Y方向偏移量
                                    $objDrawing->setOffsetY(10);
                                    $objDrawing->setWorksheet($obj->getActiveSheet());
                                } catch (\Throwable $th) {
                                    //throw $th;
                                    // echo $th->getMessage();
                                    $update['status'] = 2;
                                    $update['path'] = '';
                                    $update['msg'] =  $th->getMessage();
                                    Db::table('excel_task')->where('id',$task_id)->update($update);
                                    exit;
                                }

                            }else{
                                $sv = $name;
                            }


                        }

                    
                    }


                }


                $si++;
            }

        
            $i++;
            // echo '第'.$i.'条/'.count($data)."\n";
        }
        


        

        $length = count($title_list);
        $length2 = count($data);
        // 设置加粗和左对齐
        for ($i=0; $i < $length; $i++) { 
            # code...
            $obj->getActiveSheet()->getStyle($cellName[$i] . '1')->getFont()->setBold(true);
            // 设置第1-n行，左对齐
            for ($si = 1; $si <= $length2+1; $si++) {
                $obj->getActiveSheet()->getStyle($cellName[$i] . $si)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
            }
        }

        // 导出
        // ob_clean();
//         echo '第三';

        try {
            //code...

            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="' . $fileName . '.xlsx');
            header('Cache-Control: max-age=1');
            $objWriter = \PHPExcel_IOFactory::createWriter($obj, 'Excel2007');
        } catch (\Throwable $th) {
            //throw $th;
            // echo $th->getMessage();
            $update['status'] = 2;
            $update['path'] = '';
            $update['msg'] =  $th->getMessage().'；错误行：'.$th->getLine();
            Db::table('excel_task')->where('id',$task_id)->update($update);
            exit;
        }


//         echo '导出';
        try {
            //code...
            $objWriter->save($path);
            $update['status'] = 1;
            $update['path'] = $path_b;
            $update['msg'] = '导出成功';
        } catch (\Throwable $th) {
            //throw $th;
            $update['status'] = 2;
            $update['path'] = '';
            $update['msg'] =  $th->getMessage().'；错误行：'.$th->getLine();
        }

//         echo '导出结束';
        Db::table('excel_task')->where('id',$task_id)->update($update);
    }



    function httpcopy($url, $file="/test.jpg",$timeout=60) {
        $file = empty($file) ? pathinfo($url,PATHINFO_BASENAME) : $file;
        $dir = pathinfo($file,PATHINFO_DIRNAME);
        !is_dir($dir) && @mkdir($dir,0755,true);
        $url = str_replace(" ","%20",$url);
    
        if(function_exists('curl_init')) {
            $ch = curl_init();
            curl_setopt($ch, CURLOPT_URL, $url);
            curl_setopt($ch, CURLOPT_TIMEOUT, $timeout);
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE);
            $temp = curl_exec($ch);
            try {
                file_put_contents($file, $temp) ;
            } catch (\Throwable $th) {
                // var_dump($th);
                return ['err'=>true,'data'=>$th->getMessage().'；错误行：'.$th->getLine()];
            }

            if(@file_put_contents($file, $temp) && !curl_error($ch)) {
            //     $tofile = "/www/data/online/new_zity_php/public/admin/excel_img/".$newname;

            //     rename($file,$tofile);

               
            //    $url = "/admin/excel_img/".$newname;
            return ['err'=>false,'data'=>$file];
            } else {
                return ['err'=>true,'data'=>'下载远程图片失败'];
            }
        } 
    }
}
